#include "sql_handler.h"
#include <sstream>
#include <iostream>
#include <cstring>
#include <type_traits>

SQLHandler::SQLHandler(MySQLConnectionPool& pool) : m_pool(pool) {
    // 注册内置函数
    registerFunction("insert_register", {
        "insert into user_info (nickname,`password`,status,avatar,phone,email,register_time,last_login_time) values(?,?,0,':/chat_rooms_icon/clientlogo.png',?,?,NOW(),NOW())",
        {ParamType::STRING, ParamType::STRING,ParamType::STRING, ParamType::STRING},
        false
    });
    
    registerFunction("query_login_1", {
        "select nickname,avatar,last_login_time from user_info where username=? and password=? ",
        {ParamType::BIGINT,ParamType::STRING},
        true 
    });

    registerFunction("update_login", {
        "update user_info set status=?,last_login_time=NOW() where username= ?",
        {ParamType::INT,ParamType::BIGINT},
        false 
    });

    registerFunction("close_login", {
        "update user_info set status=0,last_login_time=NOW() where username= ?",
        {ParamType::BIGINT},
        false 
    });

    registerFunction("insert_message", {
        "insert into private_message(sender_id,receiver_id,msg_type,content,send_time,is_deleted,is_receive) values(?,?,?,?,NOW(),0,0) ",
        {ParamType::BIGINT, ParamType::BIGINT,ParamType::INT,ParamType::STRING},
        false
    });

    registerFunction("update_message", {
        "update private_message set is_receive=1 where msg_id=? ",
        {ParamType::BIGINT},
        false
    });

    registerFunction("getMessages", {
        "SELECT msg_id  FROM private_message WHERE is_receive=0",
        {},
        true
    });
    
    registerFunction("selectMessage", {
        "SELECT sender_id,receiver_id,msg_type,content,media_url,send_time,is_deleted from private_message where msg_id=?",
        {ParamType::BIGINT},
        true
    });

    registerFunction("insert_request", {
        "insert into private_member (user1,user2,who_request,relationship_type,create_time,update_time,text_count,is_recv_update) values(?,?,?,?,NOW(),NOW(),0,0)",
        {ParamType::BIGINT, ParamType::BIGINT,ParamType::INT,ParamType::INT},
        false
    });

    registerFunction("getRequests", {
        "SELECT id  FROM private_member WHERE is_recv_update=0 ",
        {},
        true
    });

    registerFunction("hasRequest", {
        "SELECT id FROM private_member WHERE user1=? and user2=? ",
        {ParamType::BIGINT, ParamType::BIGINT},
        true
    });

    registerFunction("update_count", {
        "UPDATE private_member set text_count=text_count+1 where id=?",
        {ParamType::BIGINT},
        false
    });

    registerFunction("update_request", {
        "update private_member set who_request=?,relationship_type =? where id=?",
        {ParamType::INT,ParamType::INT,ParamType::BIGINT},
        false
    });

    registerFunction("update_request_1", {
        "update private_member set is_recv_update where id=?",
        {ParamType::BIGINT},
        false
    });


    registerFunction("selectRequest", {
        "SELECT id,user1,user2,user1_last_read_time,user2_last_read_time,who_request,relationship_type,create_time,update_time,text_count from private_member where id=?",
        {ParamType::BIGINT},
        true
    });
    
    
}

SQLHandler::~SQLHandler() {}

// 注册SQL操作函数
void SQLHandler::registerFunction(const std::string& funcName, const FunctionParamsInfo& info) {
    m_functionMap[funcName] = info;
}

// 类型转换辅助函数实现
template<typename T>
T SQLHandler::convertParam(const std::string& value) {  //编译时类型分支
    if constexpr (std::is_same_v<T, int>) {
        return std::stoi(value);
    } else if constexpr (std::is_same_v<T, long long>) {  // 新增对 long long 类型的处理
        return std::stoll(value);
    }else if constexpr (std::is_same_v<T, double>) {
        return std::stod(value);
    } else if constexpr (std::is_same_v<T, bool>) {
        return value == "true" || value == "1";
    } else if constexpr (std::is_same_v<T, std::string>) {
        return value;
    } else {
        throw std::runtime_error("Unsupported parameter type");
    }
}

// 动态执行SQL操作
bool SQLHandler::execute(const std::string& funcName, const std::vector<std::string>& strParams, std::string& result) {
    auto it = m_functionMap.find(funcName);
    if (it == m_functionMap.end()) {
        std::cerr << "Function not found: " << funcName << std::endl;
        return false;
    }

    const auto& info = it->second;
    // std::cout<<"params size="<<strParams.size()<<std::endl;
    // 检查参数数量
    if (strParams.size() != info.paramTypes.size()) {
        std::cerr << "Parameter count mismatch for " << funcName 
                  << ". Expected " << info.paramTypes.size() 
                  << ", got " << strParams.size() << std::endl;
        return false;
    }
    // 转换参数类型

    std::vector<std::any> params;
    for (size_t i = 0; i < strParams.size(); ++i) {
        const auto& strParam = strParams[i];
        const auto& paramType = info.paramTypes[i];
        
        try {
            switch (paramType) {
                case ParamType::INT:
                    params.push_back(convertParam<int>(strParam));
                    break;
                case ParamType::BIGINT:
                    // std::cout<<"long long param="<<strParam<<std::endl;
                    params.push_back(convertParam<long long>(strParam));
                    break;
                case ParamType::STRING:
                    params.push_back(convertParam<std::string>(strParam));
                    break;
                case ParamType::DOUBLE:
                    params.push_back(convertParam<double>(strParam));
                    break;
                case ParamType::BOOL:
                    params.push_back(convertParam<bool>(strParam));
                    break;
                case ParamType::NULL_VALUE:
                    params.push_back(nullptr);
                    break;
            }
        } catch (const std::exception& e) {
            std::cerr << "Parameter conversion error: " << e.what() << std::endl;
            return false;
        }
    }

    // 执行SQL操作
    std::ostringstream oss;
    oss<<funcName;
    if (info.isQuery) {
        QueryResult res = executePreparedQuery(info.sqlTemplate, params);
        if (!res.success) 
        {
        result = res.error;
        return false;
        }
    
    
        // 处理非查询语句的影响行数
        if (res.rows.empty()) {
            return true;
        }
        
        // 处理查询结果（示例：拼接第一行数据）
        if (!res.rows.empty()) {
            for(const auto &row:res.rows)
            {
                for (const auto& field : row) {
                    oss<<"|"<<field;
                }
                oss<<";;;";
            }
        }
        // std::cout<<oss.str()<<std::endl;
        result=oss.str();
        return true;
    } else {
        oss<<"|success";
        result=oss.str();
        return executePreparedUpdate(info.sqlTemplate, params);
    }
}

// 执行预处理查询（带参数绑定）
QueryResult SQLHandler::executePreparedQuery(const std::string& sql, const std::vector<std::any>& params) {
    // std::cout<<"before conn "<<std::endl;
    MYSQLScopedConnection conn(m_pool);
    // std::cout << "conn valid: " << (conn.isValid() ? "true" : "false") << std::endl;
    if (!conn.isValid()) {
        std::cerr << "NULL connection obtained" << std::endl;
       return {false, "错误信息", {}, 0};  // 返回错误状态的QueryResult
    }
    
    if (mysql_set_character_set(conn.get(), "utf8mb4") != 0) {
        std::cerr << "字符集设置失败: " << mysql_error(conn.get()) << std::endl;
        return {false, "字符集错误", {}, 0};
    }
    // 验证当前字符集
    // MY_CHARSET_INFO cs;
    // mysql_get_character_set_info(conn.get(), &cs);
    // std::cout << "当前连接字符集: " << cs.csname << std::endl;  // 确保输出utf8mb4

    QueryResult result;//存储结果
    MYSQL_STMT* stmt = mysql_stmt_init(conn.get());
    if (!stmt) {
        std::cerr << "预处理语句初始化失败" << std::endl;
        return {false, "错误信息", {}, 0};  // 返回错误状态的QueryResult
    }

    std::unique_ptr<MYSQL_STMT, decltype(&mysql_stmt_close)> stmt_guard(
        stmt, mysql_stmt_close
    );
    // 准备SQL语句
    if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0) {
        result.error = "预处理SQL失败: " + std::string(mysql_stmt_error(stmt));
        return result;
    }
    // 获取参数数量
    uint32_t paramCount = mysql_stmt_param_count(stmt);
    if (paramCount != params.size()) {
        result.error = "参数数量不匹配: 需要 " + std::to_string(paramCount) + 
                      " 个，实际提供 " + std::to_string(params.size());
        return result;
    }
    // 分配参数绑定数组
    MYSQL_BIND* binds = new MYSQL_BIND[paramCount]();
    memset(binds, 0, sizeof(MYSQL_BIND) * paramCount);

    //因为string这种需要后面替换，所以要提高声明周期，否则会后序访问失效
    std::vector<std::shared_ptr<std::string>> string_copy;
    std::vector<long long> vec_ll; 
    std::vector<int> vec_int;
    // 绑定参数
    for (uint32_t i = 0; i < paramCount; i++) {
        const auto& param = params[i]; 
        
        if (param.type() == typeid(int)) {  //运行时检查any类型
            int val = std::any_cast<int>(param);
            vec_int.push_back(val);
            binds[i].buffer_type = MYSQL_TYPE_LONG;
            binds[i].buffer=&vec_int.back();
            binds[i].is_null=0;
        } else if (param.type() == typeid(long long)) {
                // 提取const long long值
             long long val = std::any_cast<long long>(param);
            vec_ll.push_back(val);
            binds[i].buffer_type = MYSQL_TYPE_LONGLONG;
            binds[i].buffer = &vec_ll.back();
            binds[i].is_null = 0;

        } else if (param.type() == typeid(std::string)) {
            const std::string& str = std::any_cast<const std::string&>(param);
            auto str_copy = std::make_shared<std::string>(str);
            string_copy.push_back(str_copy);
            binds[i].buffer_type = MYSQL_TYPE_STRING;
            binds[i].buffer = const_cast<char*>(string_copy.back()->c_str());
            binds[i].buffer_length = string_copy.back()->length();
            binds[i].is_null = 0;
            // std::cout << "  STRING参数值: " << *str_copy 
            //           << ", 地址: " << str_copy->c_str() << std::endl;
            
        } else if (param.type() == typeid(double)) {
            binds[i].buffer_type = MYSQL_TYPE_DOUBLE;
            binds[i].buffer = const_cast<double*>(std::any_cast<const double>(&param));
        } else if (param.type() == typeid(bool)) {
            int boolVal = std::any_cast<const bool>(param) ? 1 : 0;
            binds[i].buffer_type = MYSQL_TYPE_TINY;
            binds[i].buffer = &boolVal;
            binds[i].buffer_length = 1;
        } else if (param.type() == typeid(nullptr)) {
            my_bool tmp=1;
            binds[i].is_null = &tmp;
        } else {
            std::cerr << "Unsupported parameter type at position " << i << std::endl;
            delete[] binds;
            return {false, "错误信息", {}, 0};  // 返回错误状态的QueryResult
        }
    }
    // 执行参数绑定
    if (mysql_stmt_bind_param(stmt, binds) != 0) {
        result.error = "参数绑定失败: " + std::string(mysql_stmt_error(stmt));
        delete[] binds;
        return result;
    }

    // 执行查询
    if (mysql_stmt_execute(stmt) != 0) {
        result.error = "查询执行失败: " + std::string(mysql_stmt_error(stmt));
        delete[] binds;
        return result;
    }


    // 关键修改点：存储查询结果集
    if (mysql_stmt_store_result(stmt) != 0) {
        result.error = "存储结果集失败: " + std::string(mysql_stmt_error(stmt));
        delete[] binds;
        return result;
    }

    // // 获取结果集
    // std::string fixedSql = "select nickname,avatar,last_login_time from user_info where username= 1 and password= '123456' ;";
    // MYSQL* mysql = conn.get();
    // if (mysql_query(mysql, fixedSql.c_str()) != 0) {
    //     std::cerr << "查询执行失败: " << mysql_error(mysql) << std::endl;
    //     return nullptr;
    // }
    
    // MYSQL_RES* res = mysql_store_result(mysql);

    // 获取结果集行数
    long long rowCount = mysql_stmt_num_rows(stmt);
    // std::cout << "结果集包含 " << rowCount << " 行数据" << std::endl;
    
    // 获取结果集元数据（用于获取列数和列信息）
    MYSQL_RES* meta = mysql_stmt_result_metadata(stmt);
    std::unique_ptr<MYSQL_RES, decltype(&mysql_free_result)> meta_guard(meta, mysql_free_result);
    unsigned int num_fields = meta ? mysql_num_fields(meta) : 0;
    // if (!meta) {
    //     std::cerr << "获取结果集元数据失败: " << mysql_stmt_error(stmt) << std::endl;
    //     // 即使没有元数据，仍可尝试处理结果集
    // } else {
    //     // 打印列信息（可选）
    //     unsigned int num_fields = mysql_num_fields(meta);
    //     MYSQL_FIELD* fields = mysql_fetch_fields(meta);
    //     std::cout << "列信息: ";
    //     for (unsigned int i = 0; i < num_fields; i++) {
    //         std::cout << fields[i].name << " | ";
    //     }
    //     std::cout << std::endl;
    // }
    
    if (num_fields == 0) {
        // 非查询语句（如INSERT/UPDATE）
        result.rows.clear();
        result.affected_rows = mysql_stmt_affected_rows(stmt);
        result.success = true;
    }else {
    std::vector<char*> buffers; 
    std::vector<unsigned long> lengths(num_fields);
    std::vector<my_bool> is_null(num_fields, 0);
    MYSQL_BIND* resultBinds = nullptr;
    // 绑定结果集列并获取数据
    if (rowCount > 0) {
        resultBinds = new MYSQL_BIND[num_fields];
        memset(resultBinds, 0, sizeof(MYSQL_BIND) * num_fields);
        buffers.resize(num_fields);
        
        MYSQL_FIELD* fields = mysql_fetch_fields(meta);

        // std::cout<<"num_fields="<<num_fields<<std::endl;
        // 初始化绑定
        for (unsigned int i = 0; i < num_fields; i++) {
            resultBinds[i].buffer_type = MYSQL_TYPE_STRING;
            resultBinds[i].length = &lengths[i];
            resultBinds[i].is_null = &is_null[i];
            size_t buffer_size = std::max(fields[i].length, 256UL);
            buffers[i] = std::make_unique<char[]>(buffer_size).release(); // 自动管理生命周期
            resultBinds[i].buffer = buffers[i];
            resultBinds[i].buffer_length = buffer_size;
        }

        // 绑定结果集
            if (mysql_stmt_bind_result(stmt, resultBinds) != 0) {
                result.error = "绑定结果集失败: " + std::string(mysql_stmt_error(stmt));
                
                // 释放资源
                for (unsigned int i = 0; i < num_fields; i++) {
                    delete[] buffers[i];
                }
                delete[] resultBinds;
            } else {
                while (mysql_stmt_fetch(stmt) == 0) {
                    std::vector<std::string> row;
                    for (unsigned int i = 0; i < num_fields; i++) {
                        if (is_null[i]) {
                            row.push_back("NULL");
                        } else {
                            row.push_back(std::string(buffers[i], lengths[i]));
                        }
                    }
                    result.rows.push_back(row);
                }
                result.success = true;
                 // 释放资源
                for (unsigned int i = 0; i < num_fields; i++) {
                    delete[] buffers[i]; // 释放缓冲区
                }
                delete[] resultBinds;
                
            }
    }
        

    }
     // 释放资源
    delete[] binds;
    return result;
}

// 执行预处理更新（带参数绑定）
bool SQLHandler::executePreparedUpdate(const std::string& sql, const std::vector<std::any>& params) {
    MYSQLScopedConnection conn(m_pool);
    if (!conn.isValid()) return false;
    
    MYSQL_STMT* stmt_raw = mysql_stmt_init(conn.get());
    if (!stmt_raw) {
        std::cerr << "预处理语句初始化失败" << std::endl;
        return false;
    }
    std::unique_ptr<MYSQL_STMT, decltype(&mysql_stmt_close)> stmt(stmt_raw, mysql_stmt_close);

    if (mysql_set_character_set(conn.get(), "utf8mb4") != 0) {
        std::cerr << "字符集设置失败: " << mysql_error(conn.get()) << std::endl;
        return false;
    }

    // 准备SQL语句
    if (mysql_stmt_prepare(stmt.get(), sql.c_str(), sql.length()) != 0) {
        std::cerr << "预处理SQL失败: " << mysql_stmt_error(stmt.get()) << " | SQL: " << sql << std::endl;
        return false;
    }
    
    // 获取参数数量
    uint32_t paramCount = mysql_stmt_param_count(stmt.get());
    if (paramCount != params.size()) {
        std::cerr << "参数数量不匹配: 需要 " << paramCount << " 个，实际提供 " << params.size() << std::endl;
        return false;
    }
    
    // 分配参数绑定数组
    MYSQL_BIND* binds = new MYSQL_BIND[paramCount]();
    memset(binds, 0, sizeof(MYSQL_BIND) * paramCount);
    
    // 为了延长生命周期，使用容器存储临时对象
    std::vector<int> intParams;
    std::vector<long long> longlongParams(paramCount);//!!!!!!!!!这里 的参数绑定链路存在隐性断裂（因 容器扩容导致内存重分配，已绑定的 buffer 地址会失效（指向旧内存），数据库读取时获取垃圾值。）
    std::vector<double> doubleParams;
    std::vector<int> boolParams;
    std::vector<std::shared_ptr<std::string>> stringParams;
    
    // 绑定参数
    for (uint32_t i = 0; i < paramCount; i++) {
        const auto& param = params[i];
        
        if (param.type() == typeid(int)) {
            int val = std::any_cast<int>(param);
            intParams.push_back(val);
            binds[i].buffer_type = MYSQL_TYPE_LONG;
            binds[i].buffer=&intParams.back();
            binds[i].is_null=0;
        } else if (param.type() == typeid(long long)) {
            long long val = std::any_cast<long long>(param);
            std::cout<<"long long val param "<<i<<"="<<val<<std::endl;
            longlongParams.push_back(val);
            binds[i].buffer_type = MYSQL_TYPE_LONGLONG;
            binds[i].buffer = &longlongParams.back();
            binds[i].is_null = 0;
        } else if (param.type() == typeid(std::string)) {
            const std::string& str = std::any_cast<const std::string&>(param);
            auto str_copy = std::make_shared<std::string>(str);
            stringParams.push_back(str_copy);
            binds[i].buffer_type = MYSQL_TYPE_STRING;
            binds[i].buffer = const_cast<char*>(str_copy->c_str());
            binds[i].buffer_length = str_copy->length();
            binds[i].is_null = 0;
        } else if (param.type() == typeid(double)) {
            doubleParams.push_back(std::any_cast<double>(param));
            binds[i].buffer_type = MYSQL_TYPE_DOUBLE;
            binds[i].buffer = &doubleParams.back();
        } else if (param.type() == typeid(bool)) {
            boolParams.push_back(std::any_cast<bool>(param) ? 1 : 0);
            binds[i].buffer_type = MYSQL_TYPE_TINY;
            binds[i].buffer = &boolParams.back();
            binds[i].buffer_length = 1;
        } else if (param.type() == typeid(nullptr)) {
            my_bool tmp = 1;
            binds[i].is_null = &tmp;
        } else {
            std::cerr << "Unsupported parameter type at position " << i << std::endl;
            delete[] binds;
            return false;
        }
    }
    
    // 执行参数绑定
    if (mysql_stmt_bind_param(stmt.get(), binds) != 0) {
        std::cerr << "参数绑定失败: " << mysql_stmt_error(stmt.get()) << std::endl;
        delete[] binds;
        return false;
    }
    
    // 执行更新
    if (mysql_stmt_execute(stmt.get()) != 0) {
        std::cerr << "更新执行失败: " << mysql_stmt_error(stmt.get()) << std::endl;
        delete[] binds;
        return false;
    }
    
    delete[] binds;
    return true;
}

// 用户相关操作 - 带参数绑定
bool SQLHandler::insertUser(const std::string& username, const std::string& password) {
    std::vector<std::string> params = {username, password};
    std::string result;
    return execute("insertUser", params, result);
}

bool SQLHandler::checkUserExists(const std::string& username) {
    std::vector<std::string> params = {username};
    std::string result;
    bool success = execute("checkUserExists", params, result);
    return success && !result.empty() && std::stoi(result) > 0;
}

// 消息相关操作 - 带参数绑定
bool SQLHandler::insertMessage(const std::string& sender, const std::string& content) {
    std::vector<std::string> params = {sender, content};
    std::string result;
    return execute("insertMessage", params, result);
}

bool SQLHandler::getMessages(const std::string& recipient, int limit, std::string& outMessages) {
    std::vector<std::string> params = {recipient, std::to_string(limit)};
    return execute("getMessages", params, outMessages);
}

// 通用查询方法（支持自定义结果处理）
template <typename T>
bool SQLHandler::queryWithCallback(const std::string& sql, std::function<T(MYSQL_ROW)> callback, T& result) {
    QueryResult res = executePreparedQuery(sql,std::vector<std::any>{});
    if (!res.success) return false;
    
    // result = callback(mysql_fetch_row(res));
    // mysql_free_result(res);//这个函数有问题
    return true;
}

// 显式实例化常用类型
template bool SQLHandler::queryWithCallback<int>(
    const std::string& sql, std::function<int(MYSQL_ROW)> callback, int& result);

template bool SQLHandler::queryWithCallback<std::string>(
    const std::string& sql, std::function<std::string(MYSQL_ROW)> callback, 
    std::string& result);